package doInterce.impl;

import cn.hutool.core.convert.Convert;
import cn.hutool.core.io.FileUtil;
import doInterce.DoInterface;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;
import utils.DBUtil;
import utils.YmlUtil;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @Description: 解析hzb解压后的xml
 * @Author: syh
 * @Date: 2023/12/17/20:51
 */
public class DoParsing implements DoInterface {
    @Override
    public void doExecute() {
        //解析xml，并且获取到根节点的各个属性
        Map<String, Object> ymlMap = YmlUtil.getYmlMap();
        String url = Convert.toStr(ymlMap.get("url"));
        String userName = Convert.toStr(ymlMap.get("userName"));
        String password = Convert.toStr(ymlMap.get("password"));
        String dirPath = Convert.toStr(ymlMap.get("dirPath"));
        String outPath = Convert.toStr(ymlMap.get("outPath"));
        try(Connection connection = DBUtil.getConnection(url, userName, password)) {
            File[] files = new File(dirPath).listFiles();
            List<File> collect = Arrays.stream(files).filter(ele -> ele.getName().endsWith(".xml")).collect(Collectors.toList());

            StringBuffer sb = new StringBuffer();
            for(File ele: collect){
                sb.append(getXmlContent(ele, connection));
            }

            File f = new File(outPath);
            if(!f.exists()){
                f.createNewFile();
            }
            FileUtil.writeUtf8String(sb.toString(), new File(outPath));


        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static  String  getXmlContent(File file,Connection connection) throws Exception{
        StringBuffer sb = new StringBuffer();
        DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
        DocumentBuilder db = dbf.newDocumentBuilder();
        Document doc = db.parse(file);
        Element table = (Element) doc.getElementsByTagName("table").item(0);
        String tableName = table.getAttribute("name");
        NodeList columns = table.getElementsByTagName("column");
        NodeList rows = doc.getElementsByTagName("row");

        List<String> numFields = getNumFields(tableName, connection);

        for (int j = 0; j < rows.getLength(); j++) {
            Element row = (Element) rows.item(j);
            StringBuilder insertQuery = new StringBuilder("INSERT INTO " + tableName + " (");
            StringBuilder values = new StringBuilder("VALUES (");
            for (int i = 0; i < columns.getLength(); i++) {
                Element column = (Element) columns.item(i);
                String columnName = column.getAttribute("name");
                String columnValue = row.getAttribute(columnName);
                insertQuery.append(columnName);
                //数字类型或者日期类型为空字符串设置默认值
                if("".equals(columnValue) && numFields.contains(columnName.toUpperCase())){
                    columnValue = "null";
                }else{
                    columnValue = "'"+columnValue+"'";
                }

                values.append(columnValue);
                if (i < columns.getLength() - 1) {
                    insertQuery.append(", ");
                    values.append(", ");
                }
            }
            insertQuery.append(") ");
            values.append(");");
            sb.append(insertQuery.toString() + values.toString()+"\n");
        }

        return sb.toString();

    }

    public static List<String> getNumFields(String tableName, Connection conn){
        List<String> columns = new ArrayList<>();;
        try(PreparedStatement statement = conn.prepareStatement("DESCRIBE "+tableName)){
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()){
                String colName = resultSet.getString(1);
                String colType = resultSet.getString(2);

//                if(colType.contains("timestamp") ,, colType.contains("decimal") ,, colType.contains("int")){
//                    columns.add(colName.toUpperCase());
//                }
            }

            resultSet.close();
        }catch (Exception e){
            e.printStackTrace();
        }

        return columns;
    }


    public static void main(String[] args) throws Exception {

        //TB_SET_UNIT
//            StringBuffer sb = new StringBuffer();
//            DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
//            DocumentBuilder db = dbf.newDocumentBuilder();
//            Document doc = db.parse("C:\\Users\\Yi\\Desktop\\TB_SET_UNIT(TB_SET_UNIT).xml");
//            Element table = (Element) doc.getElementsByTagName("table").item(0);
//            NodeList rows = doc.getElementsByTagName("row");
//
//
//            for (int j = 0; j < rows.getLength(); j++) {
//                Element row = (Element) rows.item(j);
//                StringBuilder insertQuery = new StringBuilder("INSERT INTO TB_SET_UNIT (");
//                StringBuilder values = new StringBuilder("VALUES (");
//
//                String RPID = row.getAttribute("RPID");
//                String TBID = row.getAttribute("TBID");
//                String TBUNITID = row.getAttribute("TBUNITID");
//                String UNITID = row.getAttribute("UNITID");
//                String USERID = row.getAttribute("USERID");
//
//                String insertStatement = "INSERT INTO TB_SET_UNIT (RPID, TBID, TBUNITID, UNITID, USERID) VALUES ('" + RPID + "', '" + TBID + "', '" + TBUNITID + "', '" + UNITID + "', '" + USERID + "');";
//
//                System.out.println(insertStatement);
//            }


        //M_S01
        StringBuffer sb = new StringBuffer();
        DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
        DocumentBuilder db = dbf.newDocumentBuilder();
        Document doc = db.parse(new File("C:\\Users\\Yi\\Desktop\\单位信息(M_S01).xml"));
        Element table = (Element) doc.getElementsByTagName("table").item(0);
        NodeList Val = table.getElementsByTagName("row");

        String sql = "insert into M_S01(OUUID,UNID,RPT,TP,S0101,S0102,S0103,S0104,S0105,S0106) values('%s','%s','%s',null,'%s','%s','%s','%s','%s','%s');";

        for (int j = 0; j < Val.getLength(); j++) {
            Element row = (Element) Val.item(j);

            String value = row.getAttribute("Val");
            String[] split = value.split("[|]",-1);
            System.out.println(String.format(sql,split[0],split[1],split[2],split[4],split[5],split[6],split[7],split[8],split[9]));
//            System.out.println(String.format(sql,split[1],split[2],split[5],split[6],split[7],split[8],split[9],split[1]));

        }

    }
}
